/**
 * 设备历史状态
 */
package com.zjzywl.smoke.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.zjzywl.smoke.entity.DeviceStatusHistory;


/**
 * @author Administrator
 *
 */
@Repository
public interface DeviceStatusHistoryRepository extends JpaRepository<DeviceStatusHistory,Integer> {
	//通过状态查找历史记录
	@Query("select u from DeviceStatusHistory u where u.status=:status")
	List<DeviceStatusHistory> findHistoryByStatus(@Param("status") Integer status);
	
	//通过设备查找历史记录
	@Query("select u from DeviceStatusHistory u where u.device.id=:id")
	List<DeviceStatusHistory> findHistoryByDevice(@Param("id") Integer id);
	
	//通过时间查找历史记录
	@Query("select u from DeviceStatusHistory u where u.returnTime>=:earlyTime and u.returnTime<=:lateTime and LOCATE(:address, u.device.address)>0")
	List<DeviceStatusHistory> findHistoryByTime(@Param("earlyTime") Date earlyTime,@Param("lateTime") Date lateTime,@Param("address") String address);
	
	//通过时间,设备Id,地址查找历史记录
	@Query("select u from DeviceStatusHistory u "
			+ "where u.returnTime>=:earlyTime and u.returnTime<=:lateTime and u.device.id=:id and LOCATE(:address, u.device.address)>0")
	List<DeviceStatusHistory> findHistoryByCondition(
			@Param("earlyTime") Date earlyTime,@Param("lateTime") Date lateTime,
			@Param("id") Integer id,@Param("address") String address);
	
	//@Query("select u from DeviceStatusHistory u where u.device.address like :address")
	@Query("select u from DeviceStatusHistory u where LOCATE(:address, u.device.address)>0")
	List<DeviceStatusHistory> findHistoryByAddress(@Param("address") String address);
	
	//通过时间、设备编码查找历史记录
	@Query("select u from DeviceStatusHistory u where u.returnTime>=:earlyTime and u.returnTime<=:lateTime and u.devCode=:devCode")
	List<DeviceStatusHistory> findHistoryByTimeAndCode(@Param("earlyTime") Date earlyTime,@Param("lateTime") Date lateTime,@Param("devCode") String devCode);
	
	//通过时间、设备编码查找历史记录
	@Query("select u.temperature1,u.humidity,u.returnTime from DeviceStatusHistory u where u.returnTime>=:earlyTime and u.returnTime<=:lateTime and u.devCode=:devCode")
	List<Object> findHistoryByTimeAndCodeBySorting(@Param("earlyTime") Date earlyTime,@Param("lateTime") Date lateTime,@Param("devCode") String devCode);

	//通过时间、设备编码查找历史记录,并且数值字段不应为空
	@Query("select u.temperature1,u.humidity,u.returnTime from DeviceStatusHistory u "
			+ "where u.returnTime>=:earlyTime and u.returnTime<=:lateTime and u.devCode=:devCode and u.temperature1 is not null and u.humidity is not null ")
	List<Object> findHistoryByTimeAndCodeAndDataNotNull(@Param("earlyTime") Date earlyTime,@Param("lateTime") Date lateTime,@Param("devCode") String devCode);	
	
	@Modifying
	@Transactional
	@Query("delete from DeviceStatusHistory u where u.status=999")
	void deleteByMock();
}
